import json

from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text,desc,func,and_

app = Flask(__name__)

# MYSQL所在的主机名
host = '127.0.0.1'
# MYSQL监听的端口号
port = 3306
# 连接mysql时的用户名
username = 'root'
# 密码
password = 'root'
# mysql上创建的数据库名称
database = 'sct'

app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{username}:{password}@{host}:{port}" \
                                        f"/{database}?charset=utf8mb4"

db = SQLAlchemy(app)

# 测试连接是否成功
# with app.app_context():
#     with db.engine.connect() as conn:
#         rs = conn.execute(text("select 1"))
#         print(rs.fetchone())


@app.route("/study")
def study():
    return "学习"


@app.route("/")
def hello():
    return "您好"


class Course(db.Model):
    __table__name = 'course'
    Cno = db.Column(db.String(3),primary_key=True)
    Cname = db.Column(db.String(12))
    Chours = db.Column(db.Integer)
    Credit = db.Column(db.Float)
    Tno = db.Column(db.String(3))


@app.route("/course")
# 条件查询
def read_course():
    courses = Course.query.all()
    for course in courses:
        print(f"{course.Cname}--{course.Credit}--{course.Chours}")
    return "你家妈的你烂嘛批"


# 该函数用于打印列表中的内容
def print_list(courses):
    for course in courses:
        print(f"{course.Cname}--{course.Credit}--{course.Chours}")


# 排序
@app.route("/course/order")
def read_order():
    courses = Course.query.order_by(Course.Credit.desc())
    for i in courses:
        print(f"{i.Cname}--{i.Credit}--{i.Chours}")
    return "排序成功"

#分组求和
@app.route("/course/count")
def group_by():
    credit_count = db.session.query(Course.Credit,func.count(Course.Cname).label('c')).group_by("Credit").all()
    print(f"{credit_count}")
    return "被骗啦"


@app.route("/course/like")
def like():
    courses = Course.query.filter(and_(Course.Cname.like('%数%'),Course.Credit==12)).all()
    print_list(courses)
    return "模糊匹配"


if __name__ == '__main__':
    app.run(debug=True,port=8888)